﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Transactions;
using System.Data.SqlClient;

using Global.Class.Library;
using DataTransferObject;
using DataTransferObject.MetaDataList;
using DataAccessLayer;
//using DataAccessLayer.MetaDataList;
//using DataAccessLayer.MetaDataList.ListItemCommodityDTSTableAdapters;

using DataAccessLayer.MetaDataList;
using DataAccessLayer.MetaDataList.ListItemCommodityDTSTableAdapters;

namespace BusinessLogicLayer.MetaDataList
{
    public class ListItemCommodityBLL : NotifyPropertyChangeObject
    {
        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.ListItemCommodity; } }

        private UserInformation userOrganization;

        private ListItemCommodityMaster listItemCommodityMaster;

        private BindingList<ListItemCommodityDetail> listItemCommodityDetailList;



        public ListItemCommodityBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();


                userOrganization = new UserInformation();

                listItemCommodityMaster = new ListItemCommodityMaster();

                this.listItemCommodityDetailList = new BindingList<ListItemCommodityDetail>();

                GlobalDefaultValue.Apply(this);


                this.ListItemCommodityMaster.PropertyChanged += new PropertyChangedEventHandler(ListItemCommodityMaster_PropertyChanged);

                this.ListItemCommodityDetailList.ListChanged += new ListChangedEventHandler(ListItemCommodityDetailList_ListChanged);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        private void ListItemCommodityMaster_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            this.SetDirty();
        }

        private void ListItemCommodityDetailList_ListChanged(object sender, ListChangedEventArgs e)
        {
            this.SetDirty();
        }




        #region <Adapter>

        private ListItemCommodityListingTableAdapter listingTableAdapter;
        protected ListItemCommodityListingTableAdapter ListingTableAdapter
        {
            get
            {
                if (listingTableAdapter == null) listingTableAdapter = new ListItemCommodityListingTableAdapter();
                return listingTableAdapter;
            }
        }

        private ListItemCommodityTableAdapter masterTableAdapter;
        protected ListItemCommodityTableAdapter MasterTableAdapter
        {
            get
            {
                if (masterTableAdapter == null) masterTableAdapter = new ListItemCommodityTableAdapter();
                return masterTableAdapter;
            }
        }

        private ListItemCommodityDetailTableAdapter detailTableAdapter;
        protected ListItemCommodityDetailTableAdapter DetailTableAdapter
        {
            get
            {
                if (detailTableAdapter == null) detailTableAdapter = new ListItemCommodityDetailTableAdapter();
                return detailTableAdapter;
            }
        }


        #endregion <Adapter>

        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        public ListItemCommodityDTS.ListItemCommodityListingDataTable ListItemCommodityListing()
        {
            return this.ListingTableAdapter.GetData();
        }

        public ListItemCommodityMaster ListItemCommodityMaster
        {
            get { return this.listItemCommodityMaster; }
        }

        public BindingList<ListItemCommodityDetail> ListItemCommodityDetailList
        {
            get { return this.listItemCommodityDetailList; }
        }


        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int CommodityID   //Primary Key
        {
            get { return this.ListItemCommodityMaster.CommodityID; }
            private set
            {
                if (this.ListItemCommodityMaster.CommodityID != value)
                {
                    this.StopTracking();

                    this.ListItemCommodityMaster.CommodityID = value;

                    this.ListItemCommodityGetMaster();
                    this.ListItemCommodityGetDetail();

                    this.StartTracking();
                    this.Reset();
                }

            }
        }

        #endregion <Primary Key>

        private void ListItemCommodityGetMaster()
        {
            if (this.CommodityID > 0)
            {
                ListItemCommodityDTS.ListItemCommodityDataTable masterDataTable = this.MasterTableAdapter.GetData(this.CommodityID);

                if (masterDataTable.Count > 0)
                {
                    this.ListItemCommodityMaster.StopTracking();

                    this.ListItemCommodityMaster.EntryDate = masterDataTable[0].EntryDate;


                    this.ListItemCommodityMaster.Description = masterDataTable[0].Description;
                    this.ListItemCommodityMaster.DescriptionOfficial = masterDataTable[0].DescriptionOfficial;
                    this.ListItemCommodityMaster.DescriptionOriginal = masterDataTable[0].DescriptionOriginal;
                    this.ListItemCommodityMaster.CodeFactory = masterDataTable[0].CodeFactory;
                    this.ListItemCommodityMaster.CodeTechnical = masterDataTable[0].CodeTechnical;
                    this.ListItemCommodityMaster.Unit = masterDataTable[0].Unit;
                    this.ListItemCommodityMaster.Packing = masterDataTable[0].Packing;
                    this.ListItemCommodityMaster.Origin = masterDataTable[0].Origin;
                    this.ListItemCommodityMaster.HSCode = masterDataTable[0].HSCode;
                    this.ListItemCommodityMaster.Other = masterDataTable[0].Other;

                    this.ListItemCommodityMaster.PreviousCommodityID = masterDataTable[0].PreviousCommodityID;
                    this.ListItemCommodityMaster.ItemCategoryID = masterDataTable[0].ItemCategoryID;
                    this.ListItemCommodityMaster.ItemBrandID = masterDataTable[0].ItemBrandID;
                    this.ListItemCommodityMaster.ItemTypeID = masterDataTable[0].ItemTypeID;
                    this.ListItemCommodityMaster.SupplierID = masterDataTable[0].SupplierID;
                    this.ListItemCommodityMaster.ItemGroupID = masterDataTable[0].ItemGroupID;
                    this.ListItemCommodityMaster.ItemClassID = masterDataTable[0].ItemClassID;
                    this.ListItemCommodityMaster.ItemPM_APMID = masterDataTable[0].ItemPM_APMID;
                    this.ListItemCommodityMaster.NoExpiryDate = masterDataTable[0].NoExpiryDate;

                    this.ListItemCommodityMaster.Weight = masterDataTable[0].Weight;
                    this.ListItemCommodityMaster.LeadTime = masterDataTable[0].LeadTime;
                    this.ListItemCommodityMaster.QuantityAlert = masterDataTable[0].QuantityAlert;
                    this.ListItemCommodityMaster.QuantityReorder = masterDataTable[0].QuantityReorder;
                    this.ListItemCommodityMaster.CustomsPercent = masterDataTable[0].CustomsPercent;
                    this.ListItemCommodityMaster.ExcisePercent = masterDataTable[0].ExcisePercent;
                    this.ListItemCommodityMaster.VATPercent = masterDataTable[0].VATPercent;

                    this.ListItemCommodityMaster.InActive = masterDataTable[0].InActive = masterDataTable[0].InActive;
                    this.ListItemCommodityMaster.IsNoExpiryDate = masterDataTable[0].IsNoExpiryDate;
                    this.ListItemCommodityMaster.IsCustomsPercent = masterDataTable[0].IsCustomsPercent;
                    this.ListItemCommodityMaster.IsExcisePercent = masterDataTable[0].IsExcisePercent;
                    this.ListItemCommodityMaster.IsVATPercent = masterDataTable[0].IsVATPercent;
                    this.ListItemCommodityMaster.IsOrder = masterDataTable[0].IsOrder;
                    this.ListItemCommodityMaster.IsPromotion = masterDataTable[0].IsPromotion;
                    this.ListItemCommodityMaster.IsSample = masterDataTable[0].IsSample;
                    this.ListItemCommodityMaster.IsMerchandising = masterDataTable[0].IsMerchandising;


                    this.ListItemCommodityMaster.StartTracking();

                    this.ListItemCommodityMaster.Reset();

                    this.UserOrganization.UserID = masterDataTable[0].UserID;
                    this.UserOrganization.UserOrganizationID = masterDataTable[0].UserOrganizationID;
                }
                else throw new System.ArgumentException("Insufficient get data");
            }
            else
            {
                GlobalDefaultValue.Apply(this.ListItemCommodityMaster);
                this.ListItemCommodityMaster.EntryDate = DateTime.Today;
                this.ListItemCommodityMaster.Reset();
            }
        }


        private void ListItemCommodityGetDetail()
        {
            this.listItemCommodityDetailList.RaiseListChangedEvents = false;
            this.listItemCommodityDetailList.Clear();
            if (this.CommodityID >= 0)
            {

                ListItemCommodityDTS.ListItemCommodityDetailDataTable detailDataTable = this.DetailTableAdapter.GetData(this.CommodityID);

                if (detailDataTable.Count > 0)
                {
                    foreach (ListItemCommodityDTS.ListItemCommodityDetailRow detailRow in detailDataTable.Rows)
                    {
                        this.listItemCommodityDetailList.Add(new ListItemCommodityDetail(detailRow.CommonID, detailRow.CommonValue, detailRow.Remarks));
                    }
                }
            }
            this.listItemCommodityDetailList.RaiseListChangedEvents = true;
            this.listItemCommodityDetailList.ResetBindings();
        }

        #endregion Properties





        #region Object Permission

        public override bool IsValid
        {
            get
            {
                List<ListItemCommodityDetail> inValidListItemCommodityDetail = this.ListItemCommodityDetailList.Where(listItemCommodityDetail => !listItemCommodityDetail.IsValid).ToList();
                return this.ListItemCommodityMaster.IsValid && inValidListItemCommodityDetail.Count == 0;
            }
        }

        public bool ReadOnly
        {
            get
            {
                try
                {
                    return GlobalUserPermission.GetUserReadOnly(GlobalVariables.GlobalUserInformation.UserID, this.TaskID);
                }
                catch
                {
                    return true;
                }
            }
        }

        public bool Editable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, this.TaskID, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListItemCommodityEditable", this.CommodityID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Verifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListItemCommodityVerifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListItemCommodityEditable", this.CommodityID);
                }
                catch
                {
                    return false;
                }
            }
        }

        public bool Unverifiable
        {
            get
            {
                try
                {
                    if (!GlobalUserPermission.GetUserEditable(GlobalVariables.GlobalUserInformation.UserID, GlobalEnum.TaskID.ListItemCommodityUnverifiable, this.UserOrganization.UserOrganizationID)) return false;

                    return GlobalUserPermission.GetEditable("ListItemCommodityEditable", this.CommodityID);
                }
                catch
                {
                    return false;
                }
            }
        }


        #endregion Object Permission






        public void Fill(int staffID)
        {
            if (this.CommodityID == staffID) this.CommodityID = -1; //Enforce to reload
            this.CommodityID = staffID;
        }

        public void New()
        {
            if (this.CommodityID == 0) this.CommodityID = -1;
            this.CommodityID = 0;
        }

        public void Edit()
        {

        }

        #region Save & Delete Method


        public bool Save()
        {
            int commodityID = 0;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient save", "Save validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (this.ReadOnly) throw new System.ArgumentException("Insufficient save", "Uneditable");

                    if (!this.SaveMaster(ref commodityID)) throw new System.ArgumentException("Insufficient save", "Save master");

                    if (!this.SaveDetail(commodityID)) throw new System.ArgumentException("Insufficient save", "Save detail");

                    if (this.CommodityID <= 0)//Add
                        SQLDatabase.ExecuteNonQuery("INSERT INTO ListItemPricePolicy (CommodityID, PriceCategoryID, UPriceCUR) SELECT " + commodityID + ", PriceCategoryID, 0 FROM ListItemPriceCategory "); //UPDATE PRICE POLICY

                    transactionScope.Complete();
                }

                this.Fill(commodityID);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            if (this.listItemCommodityMaster.CommodityID <= 0) return false;

            try
            {

                if (!this.SaveValidate()) throw new System.ArgumentException("Insufficient delete", "Delete validate");

                using (TransactionScope transactionScope = new TransactionScope())
                {
                    if (!this.Editable) throw new System.ArgumentException("Insufficient delete", "Uneditable");

                    if (!this.SaveUndo(this.listItemCommodityMaster.CommodityID)) throw new System.ArgumentException("Insufficient delete", "Delete detail");

                    if (this.MasterTableAdapter.Delete(this.listItemCommodityMaster.CommodityID) != 1) throw new System.ArgumentException("Insufficient delete", "Delete master");


                    SQLDatabase.ExecuteNonQuery("DELETE FROM ListItemCommodityColumn WHERE CommodityID = " + this.CommodityID);
                    SQLDatabase.ExecuteNonQuery("DELETE FROM ListItemPricePolicy WHERE CommodityID = " + this.CommodityID);


                    transactionScope.Complete();
                }
                this.Fill(0);
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        private bool SaveValidate()
        {
            ExceptionTable exceptionTable = new ExceptionTable(new string[2, 2] { { "ExceptionCategory", "System.String" }, { "ExceptionDescription", "System.String" } });

            this.UserOrganization = GlobalUserPermission.GetUserInformation(this.UserOrganization.UserID, this.ListItemCommodityMaster.EntryDate);

            if (this.UserOrganization.UserID <= 0 || this.UserOrganization.UserOrganizationID <= 0) exceptionTable.AddException(new string[] { GlobalVariables.stringFieldRequired, "User information" });

            if (exceptionTable.Table.Rows.Count <= 0 && this.IsValid) return true; else throw new CustomException("Save validate", exceptionTable.Table);
        }


        private bool SaveMaster(ref int commodityID)
        {
            ListItemCommodityDTS.ListItemCommodityDataTable masterDataTable;
            ListItemCommodityDTS.ListItemCommodityRow masterRow;

            if (this.listItemCommodityMaster.CommodityID <= 0) //Add
            {
                masterDataTable = new ListItemCommodityDTS.ListItemCommodityDataTable();
                masterRow = masterDataTable.NewListItemCommodityRow();

                masterRow.Description = GlobalDataObjectAction.NewIDReference("ListItemCommodity", "Description", 2);
            }
            else //Edit
            {
                if (!this.SaveUndo(listItemCommodityMaster.CommodityID)) throw new System.ArgumentException("Insufficient save", "Save undo");
                masterDataTable = this.MasterTableAdapter.GetData(listItemCommodityMaster.CommodityID);
                if (masterDataTable.Count > 0) masterRow = masterDataTable[0]; else throw new System.ArgumentException("Insufficient save", "Get for edit");
            }

            masterRow.EntryDate = this.listItemCommodityMaster.EntryDate;

            masterRow.DescriptionOfficial = this.listItemCommodityMaster.DescriptionOfficial;
            masterRow.DescriptionOriginal = this.listItemCommodityMaster.DescriptionOriginal;
            masterRow.CodeFactory = this.listItemCommodityMaster.CodeFactory;
            masterRow.CodeTechnical = this.listItemCommodityMaster.CodeTechnical;
            masterRow.Unit = this.listItemCommodityMaster.Unit;
            masterRow.Packing = this.listItemCommodityMaster.Packing;
            masterRow.Origin = this.listItemCommodityMaster.Origin;
            masterRow.HSCode = this.listItemCommodityMaster.HSCode;
            masterRow.Other = this.listItemCommodityMaster.Other;

            masterRow.PreviousCommodityID = this.listItemCommodityMaster.PreviousCommodityID;
            masterRow.ItemCategoryID = this.listItemCommodityMaster.ItemCategoryID;
            masterRow.ItemBrandID = this.listItemCommodityMaster.ItemBrandID;
            masterRow.ItemTypeID = this.listItemCommodityMaster.ItemTypeID;
            masterRow.SupplierID = this.listItemCommodityMaster.SupplierID;
            masterRow.NoExpiryDate = this.listItemCommodityMaster.NoExpiryDate;
            masterRow.ItemGroupID = this.listItemCommodityMaster.ItemGroupID;
            masterRow.ItemClassID = this.listItemCommodityMaster.ItemClassID;
            masterRow.ItemPM_APMID = this.listItemCommodityMaster.ItemPM_APMID;

            masterRow.Weight = this.listItemCommodityMaster.Weight;
            masterRow.LeadTime = this.listItemCommodityMaster.LeadTime;
            masterRow.QuantityAlert = this.listItemCommodityMaster.QuantityAlert;
            masterRow.QuantityReorder = this.listItemCommodityMaster.QuantityReorder;
            masterRow.CustomsPercent = this.listItemCommodityMaster.CustomsPercent;
            masterRow.ExcisePercent = this.listItemCommodityMaster.ExcisePercent;
            masterRow.VATPercent = this.listItemCommodityMaster.VATPercent;

            masterRow.InActive = this.listItemCommodityMaster.InActive = this.listItemCommodityMaster.InActive;
            masterRow.IsNoExpiryDate = this.listItemCommodityMaster.IsNoExpiryDate;
            masterRow.IsCustomsPercent = this.listItemCommodityMaster.IsCustomsPercent;
            masterRow.IsExcisePercent = this.listItemCommodityMaster.IsExcisePercent;
            masterRow.IsVATPercent = this.listItemCommodityMaster.IsVATPercent;
            masterRow.IsOrder = this.listItemCommodityMaster.IsOrder;
            masterRow.IsPromotion = this.listItemCommodityMaster.IsPromotion;
            masterRow.IsSample = this.listItemCommodityMaster.IsSample;
            masterRow.IsMerchandising = this.listItemCommodityMaster.IsMerchandising;

            masterRow.UserID = this.UserOrganization.UserID;
            masterRow.UserOrganizationID = this.UserOrganization.UserOrganizationID;

            if (this.listItemCommodityMaster.CommodityID <= 0) masterDataTable.AddListItemCommodityRow(masterRow);

            int rowsAffected = this.MasterTableAdapter.Update(masterRow);

            commodityID = masterRow.CommodityID;

            return rowsAffected == 1;

        }


        private bool SaveDetail(int commodityID)
        {
            int serialID = 0; int rowsAffected = 0;


            #region <Save collection>

            serialID = 0;

            ListItemCommodityDTS.ListItemCommodityDetailDataTable detailDataTable = new ListItemCommodityDTS.ListItemCommodityDetailDataTable();

            foreach (ListItemCommodityDetail listItemCommodityDetail in this.ListItemCommodityDetailList)
            {
                ListItemCommodityDTS.ListItemCommodityDetailRow detailRow = detailDataTable.NewListItemCommodityDetailRow();

                detailRow.CommodityID = commodityID;
                detailRow.SerialID = ++serialID;

                detailRow.CommonID = listItemCommodityDetail.CommonID;
                detailRow.CommonValue = listItemCommodityDetail.CommonValue;

                detailRow.Remarks = listItemCommodityDetail.Remarks;

                detailDataTable.AddListItemCommodityDetailRow(detailRow);
            }

            rowsAffected = this.DetailTableAdapter.Update(detailDataTable);
            if (rowsAffected != this.listItemCommodityDetailList.Count) throw new System.ArgumentException("Insufficient save", "Save detail");


            #endregion <Save collection>


            return true;
        }


        private bool SaveUndo(int commodityID)
        {
            this.DetailTableAdapter.Delete(commodityID);

            return true;
        }



        #endregion



        private void RestoreProcedure()
        {
            string queryString;
            string[] queryArray;



            queryString = "     " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            queryString = queryString + "       SELECT      CAST(0 AS bit) AS IsCommodity, 100000 * ItemCategoryID AS CommodityID, Description, 100000 * AncestorID AS AncestorID " + "\r\n";
            queryString = queryString + "       FROM        ListItemCategory " + "\r\n";

            queryString = queryString + "       UNION ALL " + "\r\n";

            queryString = queryString + "       SELECT      CAST(1 AS bit) AS IsCommodity, CommodityID AS CommodityID, DescriptionOfficial AS Description, 100000 * ItemCategoryID AS AncestorID " + "\r\n";
            queryString = queryString + "       FROM        ListItemCommodity " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListItemCommodityListing", queryString);



            queryString = "     @CommodityID Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      CommodityID, Description, DescriptionOfficial, DescriptionOriginal, CodeFactory, CodeTechnical, PreviousCommodityID, ItemCategoryID, ItemBrandID, ItemTypeID, ItemGroupID, ItemClassID, ItemPM_APMID, SupplierID, Unit, Packing, Weight, Origin, LeadTime, HSCode, QuantityAlert, QuantityReorder, InActive, NoExpiryDate,  CustomsPercent, ExcisePercent, VATPercent, IsNoExpiryDate, IsCustomsPercent, IsExcisePercent, IsVATPercent, IsOrder, IsPromotion, IsSample, IsMerchandising, Other, EntryDate, UserID, UserOrganizationID " + "\r\n";
            queryString = queryString + "       FROM        ListItemCommodity " + "\r\n";
            queryString = queryString + "       WHERE       CommodityID = @CommodityID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListItemCommoditySelect", queryString);


            queryString = "     @Description nvarchar(50), @DescriptionOfficial nvarchar(200), @DescriptionOriginal nvarchar(200), @CodeFactory nvarchar(200), @CodeTechnical nvarchar(200), @PreviousCommodityID int, @ItemCategoryID int, @ItemBrandID int, @ItemTypeID int, @ItemGroupID int, @ItemClassID int, @ItemPM_APMID int, @SupplierID int, @Unit nvarchar(50), @Packing nvarchar(100), @Weight float, @Origin nvarchar(100), @LeadTime float, @HSCode nvarchar(50), @QuantityAlert float, @QuantityReorder float, @InActive bit, @NoExpiryDate int, @CustomsPercent float, @ExcisePercent float, @VATPercent float, @IsNoExpiryDate bit, @IsCustomsPercent bit, @IsExcisePercent bit, @IsVATPercent bit, @IsOrder bit, @IsPromotion bit, @IsSample bit, @IsMerchandising bit, @Other nvarchar(50), @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListItemCommodity (Description, DescriptionOfficial, DescriptionOriginal, CodeFactory, CodeTechnical, PreviousCommodityID, ItemCategoryID, ItemBrandID, ItemTypeID, ItemGroupID, ItemClassID, ItemPM_APMID, SupplierID, Unit, Packing, Weight, Origin, LeadTime, HSCode, QuantityAlert, QuantityReorder, InActive, NoExpiryDate, CustomsPercent, ExcisePercent, VATPercent, IsNoExpiryDate, IsCustomsPercent, IsExcisePercent, IsVATPercent, IsOrder, IsPromotion, IsSample, IsMerchandising, Other, EntryDate, UserID, UserOrganizationID) VALUES (@Description, @DescriptionOfficial, @DescriptionOriginal, @CodeFactory, @CodeTechnical, @PreviousCommodityID, @ItemCategoryID, @ItemBrandID, @ItemTypeID, @ItemGroupID, @ItemClassID, @ItemPM_APMID, @SupplierID, @Unit, @Packing, @Weight, @Origin, @LeadTime, @HSCode, @QuantityAlert, @QuantityReorder, @InActive, @NoExpiryDate, @CustomsPercent, @ExcisePercent, @VATPercent, @IsNoExpiryDate, @IsCustomsPercent, @IsExcisePercent, @IsVATPercent, @IsOrder, @IsPromotion, @IsSample, @IsMerchandising, @Other, @EntryDate, @UserID, @UserOrganizationID) " + "\r\n";
            queryString = queryString + "       SELECT      CommodityID, Description, DescriptionOfficial, DescriptionOriginal, CodeFactory, CodeTechnical, PreviousCommodityID, ItemCategoryID, ItemBrandID, ItemTypeID, ItemGroupID, ItemClassID, ItemPM_APMID, SupplierID, Unit, Packing, Weight, Origin, LeadTime, HSCode, QuantityAlert, QuantityReorder, InActive, NoExpiryDate, CustomsPercent, ExcisePercent, VATPercent, IsNoExpiryDate, IsCustomsPercent, IsExcisePercent, IsVATPercent, IsOrder, IsPromotion, IsSample, IsMerchandising, Other, EntryDate, UserID, UserOrganizationID FROM ListItemCommodity WHERE CommodityID = SCOPE_IDENTITY() " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListItemCommodityInsert", queryString);


            queryString = "     @CommodityID int, @Description nvarchar(50), @DescriptionOfficial nvarchar(200), @DescriptionOriginal nvarchar(200), @CodeFactory nvarchar(200), @CodeTechnical nvarchar(200), @PreviousCommodityID int, @ItemCategoryID int, @ItemBrandID int, @ItemTypeID int, @ItemGroupID int, @ItemClassID int, @ItemPM_APMID int, @SupplierID int, @Unit nvarchar(50), @Packing nvarchar(100), @Weight float, @Origin nvarchar(100), @LeadTime float, @HSCode nvarchar(50), @QuantityAlert float, @QuantityReorder float, @InActive bit, @NoExpiryDate int, @CustomsPercent float, @ExcisePercent float, @VATPercent float, @IsNoExpiryDate bit, @IsCustomsPercent bit, @IsExcisePercent bit, @IsVATPercent bit, @IsOrder bit, @IsPromotion bit, @IsSample bit, @IsMerchandising bit, @Other nvarchar(50), @EntryDate datetime, @UserID int, @UserOrganizationID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListItemCommodity SET Description = @Description, DescriptionOfficial = @DescriptionOfficial, DescriptionOriginal = @DescriptionOriginal, CodeFactory = @CodeFactory, CodeTechnical = @CodeTechnical, PreviousCommodityID = @PreviousCommodityID, ItemCategoryID = @ItemCategoryID, ItemBrandID = @ItemBrandID, ItemTypeID = @ItemTypeID, ItemGroupID = @ItemGroupID, ItemClassID = @ItemClassID, ItemPM_APMID = @ItemPM_APMID, SupplierID = @SupplierID, Unit = @Unit, Packing = @Packing, Weight = @Weight, Origin = @Origin, LeadTime = @LeadTime, HSCode = @HSCode, QuantityAlert = @QuantityAlert, QuantityReorder = @QuantityReorder, InActive = @InActive, NoExpiryDate = @NoExpiryDate, CustomsPercent = @CustomsPercent, ExcisePercent = @ExcisePercent, VATPercent = @VATPercent, IsNoExpiryDate = @IsNoExpiryDate, IsCustomsPercent = @IsCustomsPercent, IsExcisePercent = @IsExcisePercent, IsVATPercent = @IsVATPercent, IsOrder = @IsOrder, IsPromotion = @IsPromotion, IsSample = @IsSample, IsMerchandising = @IsMerchandising, Other = @Other, EntryDate = @EntryDate, UserID = @UserID, UserOrganizationID = @UserOrganizationID WHERE CommodityID = @CommodityID " + "\r\n";
            queryString = queryString + "       SELECT      CommodityID, Description, DescriptionOfficial, DescriptionOriginal, CodeFactory, CodeTechnical, PreviousCommodityID, ItemCategoryID, ItemBrandID, ItemTypeID, ItemGroupID, ItemClassID, ItemPM_APMID, SupplierID, Unit, Packing, Weight, Origin, LeadTime, HSCode, QuantityAlert, QuantityReorder, InActive, NoExpiryDate, CustomsPercent, ExcisePercent, VATPercent, IsNoExpiryDate, IsCustomsPercent, IsExcisePercent, IsVATPercent, IsOrder, IsPromotion, IsSample, IsMerchandising, Other, EntryDate, UserID, UserOrganizationID FROM ListItemCommodity WHERE CommodityID = @CommodityID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListItemCommodityUpdate", queryString);


            queryString = " @CommodityID int ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListItemCommodity WHERE CommodityID = @CommodityID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListItemCommodityDelete", queryString);






            queryString = " @CommodityID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      CommodityID, SerialID, CommonID, CommonValue, Remarks " + "\r\n";
            queryString = queryString + "       FROM        ListItemCommodityDetail " + "\r\n";
            queryString = queryString + "       WHERE       CommodityID = @CommodityID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListItemCommodityDetailSelect", queryString);


            queryString = " @CommodityID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       INSERT INTO ListItemCommodityDetail (CommodityID, SerialID, CommonID, CommonValue, Remarks) VALUES (@CommodityID, @SerialID, @CommonID, @CommonValue, @Remarks) " + "\r\n";
            queryString = queryString + "       SELECT      CommodityID, SerialID, CommonID, CommonValue, Remarks FROM ListItemCommodityDetail WHERE (CommodityID = @CommodityID) AND (SerialID = @SerialID) " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListItemCommodityDetailInsert", queryString);



            queryString = " @CommodityID int, @SerialID int,	@CommonID int, @CommonValue float, @Remarks nvarchar(100) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       UPDATE      ListItemCommodityDetail SET CommonID = @CommonID, CommonValue = @CommonValue, Remarks = @Remarks WHERE CommodityID = @CommodityID AND SerialID = @SerialID " + "\r\n";
            queryString = queryString + "       SELECT      CommodityID, SerialID, CommonID, CommonValue, Remarks FROM ListItemCommodityDetail WHERE CommodityID = @CommodityID AND SerialID = @SerialID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("ListItemCommodityDetailUpdate", queryString);



            queryString = " @CommodityID int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       DELETE FROM ListItemCommodityDetail WHERE CommodityID = @CommodityID " + "\r\n";
            SQLDatabase.CreateStoredProcedure("ListItemCommodityDetailDelete", queryString);



            /// <summary>
            /// Check for editable
            /// </summary>
            queryArray = new string[1];
            queryArray[0] = "   SELECT      TOP 1 CommodityID FROM InMarketDetail WHERE CommodityID = @FindIdentityID ";

            SQLDatabase.CreateProcedureToCheckExisting("ListItemCommodityEditable", queryArray);
        }

    }
}
